package com.demo.crm.springboot3security.service;

import com.demo.crm.springboot3security.entity.CustomForm;
import com.demo.crm.springboot3security.entity.FormField;
import com.demo.crm.springboot3security.entity.FormTableStructure;
import com.demo.crm.springboot3security.entity.enums.FieldType;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;

/**
 * 动态表管理服务
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class DynamicTableService {

    private final JdbcTemplate jdbcTemplate;

    /**
     * 为表单创建物理表
     */
    @Transactional
    public FormTableStructure createTableForForm(CustomForm form) {
        String tableName = "form_data_" + form.getCode().toLowerCase();
        
        // 生成建表SQL
        StringBuilder createSql = new StringBuilder();
        createSql.append("CREATE TABLE ").append(tableName).append(" (");
        createSql.append("id BIGSERIAL PRIMARY KEY,");
        createSql.append("form_data_id BIGINT NOT NULL,"); // 关联到form_data表的ID

        // 添加常用字段列
        List<String> columnDefs = new ArrayList<>();
        List<String> indexDefs = new ArrayList<>();
        
        for (FormField field : form.getFields()) {
            if (isCommonField(field)) {
                String columnDef = generateColumnDefinition(field);
                columnDefs.add(columnDef);
                
                if (shouldCreateIndex(field)) {
                    String indexDef = generateIndexDefinition(tableName, field);
                    indexDefs.add(indexDef);
                }
            }
        }

        createSql.append(String.join(",", columnDefs));
        createSql.append(");");

        // 创建表
        jdbcTemplate.execute(createSql.toString());

        // 创建索引
        for (String indexSql : indexDefs) {
            jdbcTemplate.execute(indexSql);
        }

        // 创建表结构记录
        FormTableStructure structure = new FormTableStructure();
        structure.setForm(form);
        structure.setTableName(tableName);
        structure.setCreateSql(createSql.toString());
        structure.setIndexSql(String.join(";", indexDefs));
        structure.setStatus("ACTIVE");

        return structure;
    }

    /**
     * 判断是否为常用字段（应该存储在物理表中）
     */
    private boolean isCommonField(FormField field) {
        // 根据字段类型和使用频率判断
        switch (field.getType()) {
            case TEXT:
            case NUMBER:
            case DATE:
            case TIME:
            case DATETIME:
            case SELECT:
            case RADIO:
            case CHECKBOX:
                return true;
            default:
                return false;
        }
    }

    /**
     * 判断是否应该创建索引
     */
    private boolean shouldCreateIndex(FormField field) {
        // 根据字段类型和查询需求判断
        return field.isRequired() || 
               field.getType() == FieldType.SELECT ||
               field.getType() == FieldType.DATE;
    }

    /**
     * 生成字段定义
     */
    private String generateColumnDefinition(FormField field) {
        String columnName = field.getCode().toLowerCase();
        switch (field.getType()) {
            case TEXT:
                return columnName + " VARCHAR(255)";
            case NUMBER:
                return columnName + " NUMERIC";
            case DATE:
                return columnName + " DATE";
            case TIME:
                return columnName + " TIME";
            case DATETIME:
                return columnName + " TIMESTAMP";
            case SELECT:
            case RADIO:
                return columnName + " VARCHAR(50)";
            case CHECKBOX:
                return columnName + " VARCHAR(255)";
            default:
                return columnName + " TEXT";
        }
    }

    /**
     * 生成索引定义
     */
    private String generateIndexDefinition(String tableName, FormField field) {
        String columnName = field.getCode().toLowerCase();
        return String.format("CREATE INDEX idx_%s_%s ON %s (%s);",
                tableName, columnName, tableName, columnName);
    }
} 